import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {

    private Logger logger = LoggerFactory.getLogger(StudentDao.class);


    //添加学生信息
    public void add(Student stu){
        String sql = "insert into t_student(name,age) values(?,?)";
        Connection conn = null;
        Statement st = null;
        PreparedStatement ps = null;
        try{
            conn = JDBCUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setObject(1,stu.getName());
            ps.setObject(2,stu.getAge());
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(null,ps,conn);
        }
    }

    //删除学生信息
    public void delete(int id){
        String sql = "delete from t_student where id=?";
        Connection conn = null;
        Statement st = null;
        PreparedStatement ps = null;
        try{
            conn = JDBCUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setObject(1,id);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(null,ps,conn);
        }
    }

    //修改学生信息
    public void update(Student stu){
        String sql = "update t_student set name =?,age=? where id=?";
        Connection conn = null;
        Statement st = null;
        PreparedStatement ps = null;
        try{
            conn = JDBCUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setObject(1,stu.getName());
            ps.setObject(2,stu.getAge());
            ps.setObject(1,stu.getId());
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(null,ps,conn);
        }
    }

    //查询
    public Student get(int id){
        String sql = "select * from t_student where id=?";
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        try{
            conn = JDBCUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setObject(1,id);
            rs = ps.executeQuery();
            if(rs.next()){
                logger.info("res:{}",rs);
                String name = rs.getString("name");
                int age = rs.getInt("age");
                Student stu = new Student(id,name,age);
                return stu;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(null,ps,conn);
        }

        return null;
    }

    //查询
    public List<Student> list(){
        List<Student> list = new ArrayList<Student>();
        String sql = "select * from t_student ";
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        try{
            conn = JDBCUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
//                logger.info("res:{}",rs);
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                Student stu = new Student(id,name,age);
                list.add(stu);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(null,ps,conn);
        }
        return list;
    }
}
